package com.mairuide.multipleData.service;

import com.mairuide.casemanage.service.CaseManageService;
import com.mairuide.multipleData.dto.MultipleDataDto;
import com.mairuide.multipleData.pojo.MultipleDataBill;
import com.mairuide.multipleData.pojo.MultipleDataPhone;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;

/**
 * @Description
 * @auther 刘中华
 * @create 2019-06-25 16:28
 */
@Service
public class PhoneMultipleDataService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private CaseManageService caseManageService;

    public List<Map<String, Object>> getPhoneInfoByServerNumber(String serverNumber){
        String sql="select ID_NUMBER,SERVICE_USER_INFORMATION from TB_PHONE_RECORDING WHERE ID_NUMBER is not null AND SERVICE_NUMBER='"+serverNumber+"' AND rownum=1 ";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return list;
    }
    /**
     * @Author 刘中华
     * @Date 2019/6/25 16:30
     * @Param [beginTime, endTime, time_interval]
     * @return java.util.List
     * @Description  资金交易时的通话分析
     **/
    public List<MultipleDataPhone> findZijinTransaction(MultipleDataDto dto){
        long start = System.currentTimeMillis();
        String beginTime = dto.getBeginTime();
        String endTime = dto.getEndTime();
        String casemanageId = dto.getCasemanageId();
        String accountBodyCard = dto.getAccountBodyCard();
        Integer time_interval = dto.getTime_interval();
        String serviceNumber = dto.getServiceNumber();
        String id_number = "";
        String service_user_information = "";

        String whichTable = caseManageService.getWhichTable(casemanageId);

        List<Map<String, Object>> phoneInfoByServerNumber = getPhoneInfoByServerNumber(serviceNumber);
        if (phoneInfoByServerNumber.size()!=0) {
            Map<String, Object> map = phoneInfoByServerNumber.get(0);
            id_number = (String) map.get("ID_NUMBER");
        }
        //第一步，先查出账单交易的时间列表
        String sql_time="SELECT to_char(TRADE_DATE - "+time_interval+"/24/60,'yyyy-mm-dd hh24:mi:ss') min_time,to_char(TRADE_DATE + "+time_interval+"/24/60,'yyyy-mm-dd hh24:mi:ss') max_time,TRADE_DATE,ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_ADDRESS,ID_NUMBER ";
        sql_time+=" FROM " + whichTable;
        sql_time+=" WHERE  TRADE_DATE is not null AND CASEMANAGE_ID = '" + casemanageId+"' ";
        if (StringUtils.isNotBlank(id_number)){
            sql_time+=" and id_number = '" + id_number+"' ";
        }else{
            sql_time+=" and 1=0 ";
        }
        if (StringUtils.isNotBlank(beginTime)){
            sql_time+=" and to_char( TRADE_DATE, 'yyyy-mm-dd' ) >= '" + beginTime +"' ";
        }
        if (StringUtils.isNotBlank(endTime)){
            sql_time+=" and to_char( TRADE_DATE, 'yyyy-mm-dd' ) <= '" + endTime +"' ";
        }
        sql_time+=" GROUP BY TRADE_DATE,ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_ADDRESS,ID_NUMBER ORDER BY TRADE_DATE";
        List<MultipleDataPhone> time_list = jdbcTemplate.query(sql_time,new BeanPropertyRowMapper<MultipleDataPhone>(MultipleDataPhone.class));
        //第二步，遍历账单对象  根据时间波动来查找该范围内的数据
        for (int i = 0; i < time_list.size(); i++) {
            MultipleDataPhone multipleDataPhone = time_list.get(i);
            String min_time = multipleDataPhone.getMin_time();
            String max_time = multipleDataPhone.getMax_time();
            String account_body = multipleDataPhone.getAccount_body();

            String sql_phone=" SELECT SERVICE_NUMBER,SERVICE_USER_INFORMATION,count( 1 ) count FROM TB_PHONE_RECORDING ";
            sql_phone +=" WHERE CASEMANAGE_ID = '"+casemanageId+"' ";
            if (StringUtils.isNotBlank(id_number)){
                sql_phone+=" AND ID_NUMBER = '"+id_number+"' ";
            }
            sql_phone+=" AND (to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss')>= '"+min_time +"' and to_char(STARTING_TIME,'yyyy-mm-dd hh24:mi:ss')<='" + max_time+"') ";
            sql_phone+=" GROUP BY SERVICE_USER_INFORMATION,SERVICE_NUMBER";

            List<Map<String, Object>> phone_list = jdbcTemplate.queryForList(sql_phone);
            if (phone_list.size()>0){
                Map<String, Object> phone = phone_list.get(0);
                String serviceUserInformation = (String)phone.get("SERVICE_USER_INFORMATION");
                String service_number = (String)phone.get("SERVICE_NUMBER");
                Integer count = ((BigDecimal) phone.get("count")).intValue();
                multipleDataPhone.setCount(count);
                multipleDataPhone.setService_number(service_number);
                multipleDataPhone.setPhoneOwnerName(serviceUserInformation);
            }
        }
        String sort = dto.getSort();
        String order = dto.getOrder();
        if (StringUtils.isNotBlank(sort)&&StringUtils.isNotBlank(order)){
            Collections.sort(time_list,new Comparator<MultipleDataPhone>() {
                public int compare(MultipleDataPhone o1, MultipleDataPhone o2) {
                    if ("count".equals(sort)){
                        Integer count = o1.getCount()==null?0:o1.getCount();
                        Integer count1 = o2.getCount()==null?0:o2.getCount();
                        if ("asc".equals(order)){
                            return count-count1;
                        }else{
                            return count1-count;
                        }
                    }else if ("trade_date".equals(sort)){
                        String trade_date = o1.getTrade_date();
                        String trade_date1 = o2.getTrade_date();
                        if ("asc".equals(order)){
                            return trade_date.compareTo(trade_date1);
                        }else{
                            return trade_date1.compareTo(trade_date);
                        }
                    }else{
                        return 0;
                    }

                }
            });
        }
        System.out.println("本次查询花费"+(System.currentTimeMillis()-start)/1000+" s");
        return time_list;
    }
    /**
     * @Author 刘中华
     * @Date 2019/6/25 16:30
     * @Param [beginTime, endTime, time_interval]
     * @return java.util.List
     * @Description  通过话单的资金交易分析
     **/
    public List findPhoneTransaction(MultipleDataDto dto){
        String beginTime = dto.getBeginTime();
        String endTime = dto.getEndTime();
        String casemanageId = dto.getCasemanageId();
        String service_number = dto.getServiceNumber();
        Integer time_interval = dto.getTime_interval();
        String whichTable = caseManageService.getWhichTable(casemanageId);
        //第一步，先查出话单交易的时间列表
        String sql_time="SELECT to_char(STARTING_TIME - "+time_interval+"/24,'yyyy-mm-dd hh24:mi:ss') min_time,to_char(STARTING_TIME + "+time_interval+"/24,'yyyy-mm-dd hh24:mi:ss') max_time,SERVICE_NUMBER,SERVICE_USER_INFORMATION,PARTY_NUMBER,PARTY_USER_INFORMATION,STARTING_TIME,TALK_TIME,ID_NUMBER ";
        sql_time+=" FROM TB_PHONE_RECORDING ";
        sql_time+=" WHERE STARTING_TIME is not null  AND CASEMANAGE_ID = " + casemanageId;
        if (StringUtils.isNotBlank(service_number)){
            sql_time+=" and service_number = '" + service_number+"' ";
        }else{
            sql_time+=" and 1=0 ";
        }
        if (StringUtils.isNotBlank(beginTime)){
            sql_time+=" and to_char( STARTING_TIME, 'yyyy-mm-dd' ) >= '" + beginTime +"' ";
        }
        if (StringUtils.isNotBlank(endTime)){
            sql_time+=" and to_char( STARTING_TIME, 'yyyy-mm-dd' ) <= '" + endTime +"' ";
        }
        sql_time+=" GROUP BY SERVICE_NUMBER,SERVICE_USER_INFORMATION,PARTY_NUMBER,PARTY_USER_INFORMATION,STARTING_TIME,TALK_TIME,ID_NUMBER ORDER BY STARTING_TIME";
        List<MultipleDataBill> time_list = jdbcTemplate.query(sql_time,new BeanPropertyRowMapper<MultipleDataBill>(MultipleDataBill.class));
        //第二步，遍历账单对象  根据时间波动来查找该范围内的数据
        for (int i = 0; i < time_list.size(); i++) {
            MultipleDataBill multipleDataPhone = time_list.get(i);
            String min_time = multipleDataPhone.getMin_time();
            String max_time = multipleDataPhone.getMax_time();
            String service_user_information = multipleDataPhone.getService_user_information();
            String id_number = multipleDataPhone.getId_number();

            String sql_bill=" SELECT count( 1 ) count,sign,sum(ABS( ACCOUNT_MONEY )) money  FROM "+ whichTable;
            sql_bill +=" WHERE sign IS NOT NULL and CASEMANAGE_ID = '"+casemanageId+"' ";
            if (StringUtils.isNotBlank(id_number)){
                sql_bill+=" AND ID_NUMBER = '"+id_number+"' ";
            }else{
                sql_bill+=" AND 1=0 ";
            }
            sql_bill+=" AND (to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss')>= '"+min_time +"' and to_char(TRADE_DATE,'yyyy-mm-dd hh24:mi:ss')<='" + max_time+"') ";
            sql_bill+=" GROUP BY sign ORDER BY SIGN";

            List<Map<String, Object>> bill_list = jdbcTemplate.queryForList(sql_bill);
            Integer count=0;
            for (int j = 0; j < bill_list.size(); j++) {
                Map<String, Object> m = bill_list.get(j);
                Integer sign = ((BigDecimal) m.get("sign")).intValue();
                count += ((BigDecimal) m.get("count")).intValue();
                BigDecimal money = (BigDecimal) m.get("money");
                if (sign==0){
                    multipleDataPhone.setIn_money(money);
                }else{
                    multipleDataPhone.setOut_money(money);
                }
            }
            multipleDataPhone.setCount(count);
        }
        String sort = dto.getSort();
        String order = dto.getOrder();
        if (StringUtils.isNotBlank(sort)&&StringUtils.isNotBlank(order)){
            Collections.sort(time_list,new Comparator<MultipleDataBill>() {
                public int compare(MultipleDataBill o1, MultipleDataBill o2) {
                    if ("count".equals(sort)){
                        Integer count = o1.getCount()==null?0:o1.getCount();
                        Integer count1 = o2.getCount()==null?0:o2.getCount();
                        if ("asc".equals(order)){
                            return count-count1;
                        }else{
                            return count1-count;
                        }
                    }else if ("starting_time".equals(sort)){
                        String trade_date = o1.getStarting_time();
                        String trade_date1 = o2.getStarting_time();
                        if ("asc".equals(order)){
                            return trade_date.compareTo(trade_date1);
                        }else{
                            return trade_date1.compareTo(trade_date);
                        }
                    }else if ("talk_time".equals(sort)){
                        Integer talk_time = o1.getTalk_time()==null?0:o1.getTalk_time();
                        Integer talk_time1 = o2.getTalk_time()==null?0:o2.getTalk_time();
                        if ("asc".equals(order)){
                            return talk_time-talk_time1;
                        }else{
                            return talk_time1-talk_time;
                        }
                    }else{
                        return 0;
                    }

                }
            });
        }
        return time_list;
    }
}
